{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "if issues install conda install -c conda-forge openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Excel File\n",
    "filename = 'data/car_financing.xlsx'\n",
    "df = pd.read_excel(filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Filtering \n",
    "car_filter = df['car_type']=='Toyota Sienna'\n",
    "interest_filter = df['interest_rate']==0.0702\n",
    "df = df.loc[car_filter & interest_filter, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Approach 1 dictionary substitution using rename method\n",
    "df = df.rename(columns={'Starting Balance': 'starting_balance',\n",
    "                        'Interest Paid': 'interest_paid', \n",
    "                        'Principal Paid': 'principal_paid',\n",
    "                        'New Balance': 'new_balance'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Approach 2 list replacement\n",
    "# Only changing Month -> month, but we need to list the rest of the columns\n",
    "df.columns = ['month',\n",
    "              'starting_balance',\n",
    "              'Repayment',\n",
    "              'interest_paid',\n",
    "              'principal_paid',\n",
    "              'new_balance',\n",
    "              'term',\n",
    "              'interest_rate',\n",
    "              'car_type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Approach 1\n",
    "# This approach allows you to drop multiple columns at a time \n",
    "df = df.drop(columns=['term'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Approach 2 use the del command\n",
    "del df['Repayment']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# missing values can be excluded in calculations by default. \n",
    "# excludes missing values in the calculation \n",
    "interest_missing = df['interest_paid'].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill in with the actual value\n",
    "df.loc[interest_missing,'interest_paid'] = 93.24"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export Pandas DataFrames to csv and excel files "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export DataFrame to csv File\n",
    "df.to_csv(path_or_buf='data/table_i702t60.csv',\n",
    "          index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you get an error below, you probably need to install openpyxl or similar. \n",
    "\n",
    "stackoverflow: https://stackoverflow.com/questions/34509198/no-module-named-openpyxl-python-3-4-ubuntu\n",
    "\n",
    "`conda install openpyxl` or \n",
    "\n",
    "`conda install -c anaconda openpyxl`\n",
    "`pip install openpyxl`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you get the error below, try installing a library\n",
    "# Export DataFrame to excel File\n",
    "df.to_excel(excel_writer='data/table_i702t60.xlsx',\n",
    "            index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep in mind that if you dont know a methods parameters,\n",
    "# you can look them up using the help command. \n",
    "help(df.to_csv)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also good idea to check your exported files."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
